﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;

using ACRODISTXLib;

using AKMII.DMRA.Business.Management;
using AKMII.DMRA.Business.Rule;
using AKMII.DMRA.Common;
using AKMII.DMRA.DataAccess;

using MapPoint;

using Microsoft.Office.Interop.Excel;

using Constants = AKMII.DMRA.Common.Constants;

namespace AKMII.DMRA.Business.Service
{
    public class ExcelReportService
    {
        Microsoft.Office.Interop.Excel.Application excelApplication = null;
        Workbooks workBooks = null;
        Workbook workBook = null;
        Worksheet workSheet = null;
        Range rans = null;
        Range ran = null;
        string DncDate;

        public void GenerateReport(string path, List<Lead> leads, List<string> codes, bool isNeedOrder)
        {
            try
            {
                Logger.Info("Start generate report at" + path);

                object o = System.Reflection.Missing.Value;
                excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApplication.Visible = false;
                workBooks = excelApplication.Workbooks;
                workBook = workBooks.Add(o);
                workSheet = (Worksheet)workBook.ActiveSheet;

                for (int i = 0; i < codes.Count - 3; i++)
                {
                    workSheet.Copy(o, workSheet);
                }
                for (int i = 1; i <= codes.Count; i++)
                {
                    List<Lead> filterLeads = new List<Lead>();

                    if (isNeedOrder)
                    {
                        filterLeads = leads.OrderBy(c => c.SubSequenceNumber).Where(c => c.LeadBlockCode == codes[i - 1]).ToList<Lead>();
                    }
                    else
                    {
                        filterLeads = leads.Where(c => c.LeadBlockCode == codes[i - 1]).ToList<Lead>();
                    }

                    workSheet = (Worksheet)workBook.Sheets[i];
                    workSheet.Name = codes[i - 1];

                    if (filterLeads.Count > 0)
                    {
                        foreach (var lead in filterLeads)
                        {
                            if (!string.IsNullOrEmpty(lead.DncAfterDate) && !string.IsNullOrEmpty(lead.DncAfterDate.Trim()))
                            {
                                DncDate = lead.DncAfterDate;
                                break;
                            }
                        }
                    }

                    SetPageSetup();
                    rans = (Range)workSheet.Cells;

                    SetColumnWidth();

                    //FillReportHeader(filterLeads.Count > 0 ? filterLeads[0] : null);
                    FillReportTitle();
                    FillReportValue(filterLeads);
                }

                workBook.SaveCopyAs(path);


                Logger.Info("End generate report at" + path);
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);
            }
            finally
            {
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
            }
        }

        public void GenerateReport(string path, List<Lead> leads)
        {
            try
            {
                if (leads != null && leads.Count > 0)
                {
                    Logger.Info("Start generate report at" + path);

                    object o = System.Reflection.Missing.Value;
                    excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excelApplication.Visible = false;
                    workBooks = excelApplication.Workbooks;
                    workBook = workBooks.Add(o);
                    workSheet = (Worksheet)workBook.ActiveSheet;
                    workSheet = (Worksheet)workBook.Sheets[1];
                    workSheet.Name = leads[0].LeadBlockCode;

                    foreach (var lead in leads)
                    {
                        if (!string.IsNullOrEmpty(lead.DncAfterDate) && !string.IsNullOrEmpty(lead.DncAfterDate.Trim()))
                        {
                            DncDate = lead.DncAfterDate;
                            break;
                        }
                    }

                    SetPageSetup();
                    rans = (Range)workSheet.Cells;
                    SetColumnWidth();
                    FillReportTitle();
                    FillReportValue(leads);
                    workBook.SaveCopyAs(path);

                    workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, path.Replace(".xls", ".ps"));
                    Utility.ConvertToPdf(path.Replace(".xls", ".ps"), path.Replace(".xls", ".pdf"));

                    Logger.Info("End generate report at" + path);
                }
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);
            }
            finally
            {
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
            }
        }

        public void GenerateReport(string path, List<LeadBlock> requesterCodes)
        {
            try
            {
                object o = System.Reflection.Missing.Value;
                excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApplication.Visible = false;
                workBooks = excelApplication.Workbooks;
                workBook = workBooks.Add(o);

                workSheet = (Worksheet)workBook.ActiveSheet;

                var pairs = (from requesterCode in requesterCodes
                             orderby requesterCode.County
                             group requesterCode by new { requesterCode.Market, requesterCode.County }
                                 into results
                                 select new { results.Key });

                List<Pair<string, string>> marketsTerrs = new List<Pair<string, string>>();
                foreach (var pair in pairs)
                {
                    marketsTerrs.Add(new Pair<string, string>(pair.Key.Market, pair.Key.County));
                }

                for (int i = 0; i < marketsTerrs.Count - 3; i++)
                {
                    workSheet.Copy(o, workSheet);
                }

                for (int i = 1; i <= marketsTerrs.Count; i++)
                {
                    List<LeadBlock> filterCodes = requesterCodes.Where(c => c.County == marketsTerrs[i - 1].Second
                        && c.Market == marketsTerrs[i - 1].First).ToList<LeadBlock>();
                    workSheet = (Worksheet)workBook.Sheets[i];
                    workSheet.Name = string.Format(AKMII.DMRA.Common.Constants.SheetNameForSummaryReport, marketsTerrs[i - 1].First, marketsTerrs[i - 1].Second);
                    SetPageSetupForSummaryReport();

                    rans = (Range)workSheet.Cells;
                    SetColumnWidthForSummaryReport();

                    FillReportTitleForSummaryReport();
                    FillReportValue(filterCodes);
                }

                workBook.SaveCopyAs(path);

                Logger.Info("Generate report at" + path);
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);

                SendMail(path);
            }
            finally
            {
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
            }
        }

        public void GenerateReport(string path, string assignmentName)
        {
            try
            {
                Logger.Info("Start generate blank report at " + path);

                string templatePath = Environment.CurrentDirectory + @"\Lead Detail report-Blank.xls";
                if (!File.Exists(templatePath))
                {
                    Logger.Error("New blank lead detail report template file doesn't exist.");
                    return;
                }

                object o = System.Reflection.Missing.Value;
                excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApplication.Visible = false;
                workBooks = excelApplication.Workbooks;
                workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);

                workSheet = (Worksheet)workBook.Sheets[1];
                workSheet.Name = assignmentName;
                rans = (Range)workSheet.Cells;

                SetRangeValue(2, 15, assignmentName, ran, rans);

                workBook.SaveCopyAs(path);

                workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, path.Replace(".xls", ".ps"));
                Utility.ConvertToPdf(path.Replace(".xls", ".ps"), path.Replace(".xls", ".pdf"));

                Logger.Info("End generate blank report at " + path);
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate blank report file", ex);
            }
            finally
            {
                #region Release the excel related object
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
                #endregion
            }
        }

        public void GenerateReport(string path, List<Lead> leads, string assignmentName)
        {
            try
            {
                if (leads != null && leads.Count > 0)
                {
                    Logger.Info("Start generate report at " + path);

                    string templatePath = Environment.CurrentDirectory + @"\LeadDetailTemplate.xls";
                    if (!File.Exists(templatePath))
                    {
                        Logger.Error("New lead detail report template file doesn't exist.");
                        return;
                    }

                    object o = System.Reflection.Missing.Value;
                    excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excelApplication.Visible = false;
                    workBooks = excelApplication.Workbooks;
                    workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);

                    workSheet = (Worksheet)workBook.Sheets[1];
                    workSheet.Name = assignmentName;
                    rans = (Range)workSheet.Cells;

                    FillReportWithNewTemplate(leads, assignmentName);

                    //workSheet = (Worksheet)workBook.Sheets[2];
                    //rans = (Range)workSheet.Cells;

                    //FillReportHeaderWithNewTemplate(leadBlock);

                    workBook.SaveCopyAs(path);

                    workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, path.Replace(".xls", ".ps"));
                    Utility.ConvertToPdf(path.Replace(".xls", ".ps"), path.Replace(".xls", ".pdf"));

                    Logger.Info("End generate report at " + path);
                }
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file with new template", ex);
            }
            finally
            {
                #region Release the excel related object
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
                #endregion
            }
        }

        public void GenerateReport(List<OrderInfo> orderInfos, string path)
        {
            Workbook workbook = null;
            Worksheet worksheet = null;
            object o = System.Reflection.Missing.Value;

            try
            {
                if (orderInfos != null && orderInfos.Count > 0)
                {
                    excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    workbook = excelApplication.Workbooks.Add(o);

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = "Order information";

                    ((Range)worksheet.Columns[3, Type.Missing]).ColumnWidth = 23;
                    ((Range)worksheet.Columns[4, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[5, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[6, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[7, Type.Missing]).ColumnWidth = 12;
                    ((Range)worksheet.Columns[8, Type.Missing]).ColumnWidth = 12;

                    worksheet.Cells[1, 1] = "Division";
                    worksheet.Cells[1, 2] = "Market";
                    worksheet.Cells[1, 3] = "Marketing Director Name";
                    worksheet.Cells[1, 4] = "# Agents Listed On\r\nAlpha Roster\r\nPer MD";
                    worksheet.Cells[1, 5] = "# Agents\r\nOrdered From\r\nAlpha Roster";
                    worksheet.Cells[1, 6] = "# Unique\r\nAgents\r\nCreated";
                    worksheet.Cells[1, 7] = "# Unique\r\nAgents\r\nOrdered";
                    worksheet.Cells[1, 8] = " Total #\r\nAssignments\r\nOrdered For\r\nAll Agents";
                    worksheet.Cells[1, 9] = "Active Lead\r\nCount";
                    worksheet.Cells[1, 10] = "Active Stop\r\nCount";
                    worksheet.Cells[1, 11] = "Lapsed Lead\r\nCount";
                    worksheet.Cells[1, 12] = "Lapsed Stop\r\nCount";
                    worksheet.Cells[1, 13] = "Neighbor Lead\r\nCount";
                    worksheet.Cells[1, 14] = "Neighbor Stop\r\nCount";
                    worksheet.Cells[1, 15] = "Business Lead\r\nCount";
                    worksheet.Cells[1, 16] = "Business Stop\r\nCount";
                    worksheet.Cells[1, 17] = "Affirmation\r\nApproval\r\n(Y/N)";
                    worksheet.Cells[1, 18] = "Date MD Submitted\r\nOrder";

                    for (int j = 2; j < orderInfos.Count + 2; j++)
                    {
                        for (int k = 1; k < 19; k++)
                        {
                            switch (k)
                            {
                                case 1:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Division;
                                    break;
                                case 2:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].Market;
                                    break;
                                case 3:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AccountName;
                                    break;
                                case 4:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentAlphaRosterCount;
                                    break;
                                case 5:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AlphaRosterOrderCount;
                                    break;
                                case 6:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentCreatedCount;
                                    break;
                                case 7:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].AgentCreatedOrderCount;
                                    break;
                                case 8:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].TotalAssignments;
                                    break;
                                case 9:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].ActiveLeadCount;
                                    break;
                                case 10:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].ActiveStopCount;
                                    break;
                                case 11:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].LapsedLeadCount;
                                    break;
                                case 12:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].LapsedStopCount;
                                    break;
                                case 13:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].NeighborLeadCount;
                                    break;
                                case 14:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].NeighborStopCount;
                                    break;
                                case 15:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].BusinessLeadCount;
                                    break;
                                case 16:
                                    worksheet.Cells[j, k] = orderInfos[j - 2].BusinessStopCount;
                                    break;
                                case 17:
                                    worksheet.Cells[j, k] = "Y";
                                    break;
                                case 18:
                                    worksheet.Cells[j, k] = string.Format("{0}/{1}/{2}", orderInfos[j - 2].UpdateTS.Day,
                                         orderInfos[j - 2].UpdateTS.Month, orderInfos[j - 2].UpdateTS.Year);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    excelApplication.DisplayAlerts = false;
                    workbook.SaveCopyAs(path);
                }
            }
            catch (System.Exception ex)
            {
                Logger.Error("Exception occurs in generate excel report for order information", ex);
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Close(false, o, o);
                }
                if (excelApplication.Workbooks != null)
                {
                    excelApplication.Workbooks.Close();
                }
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                IntPtr point = new IntPtr(excelApplication.Hwnd);
                int processId = 0;
                GetWindowThreadProcessId(point, out processId);
                Process p = Process.GetProcessById(processId);
                p.Kill();
            }
        }

        
        /*
        /// <summary>
        /// Generate agent potential report
        /// </summary>
        public void GenerateAgentPotentialReport(string directory, string account, int month, int year, LeadBlock leadBlock)
        {
            try
            {
                string templatePath = Environment.CurrentDirectory + @"\AssignmentPotentialTemplate.xlsx";
                if (!File.Exists(templatePath))
                {
                    Logger.Error("Agent Potential report template file doesn't exist.");
                    return;
                }

                try
                {
                    object o = System.Reflection.Missing.Value;
                    excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excelApplication.Visible = false;
                    workBooks = excelApplication.Workbooks;
                    workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
                    
                    workSheet = (Worksheet)workBook.Sheets[1];
                    rans = (Range)workSheet.Cells;
                    FillAgentPotentialReport(leadBlock);

                    string finalPath = Path.Combine(directory, string.Format("AgentPotential_{0}_{1}.xls", leadBlock.UserId, leadBlock.LeadBlockCode));
                    workBook.SaveCopyAs(finalPath);

                    workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, finalPath.Replace(".xls", ".ps"));
                    Utility.ConvertToPdf(finalPath.Replace(".xls", ".ps"), finalPath.Replace(".xls", ".pdf"));

                    Logger.Info("Generate agent potential report at " + finalPath);
                }
                catch (Exception ex)
                {
                    Logger.Error("Exception occurs in generate report file", ex);
                }
                finally
                {
                    #region Release the excel related object
                    if (ran != null)
                    {
                        Marshal.ReleaseComObject(ran);
                        rans = null;
                    }

                    if (rans != null)
                    {
                        Marshal.ReleaseComObject(rans);
                        rans = null;
                    }

                    if (workSheet != null)
                    {
                        Marshal.ReleaseComObject(workSheet);
                        workSheet = null;
                    }

                    if (workBook != null)
                    {
                        workBook.Close(false, Type.Missing, Type.Missing);
                        Marshal.ReleaseComObject(workBook);
                        workBook = null;
                    }

                    if (workBooks != null)
                    {
                        workBooks.Close();
                        Marshal.ReleaseComObject(workBooks);
                        workBooks = null;
                    }

                    if (excelApplication != null)
                    {
                        excelApplication.Quit();
                    }

                    ExitExcelProcess();
                    #endregion
                }

            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);
            }
        }
        */
        /// <summary>
        /// Generate agent potential report
        /// </summary>
        public void GenerateAgentPotentialReport(string directory, string account, int month, int year, Assignment assignment)
        {
            try
            {
                string templatePath = Environment.CurrentDirectory + @"\AssignmentPotentialTemplate.xlsx";
                if (!File.Exists(templatePath))
                {
                    Logger.Error("Agent Potential report template file doesn't exist.");
                    return;
                }

                try
                {
                    object o = System.Reflection.Missing.Value;
                    excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excelApplication.Visible = false;
                    workBooks = excelApplication.Workbooks;
                    workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);

                    workSheet = (Worksheet)workBook.Sheets[1];
                    rans = (Range)workSheet.Cells;
                    FillAgentPotentialReport(assignment);

                    string finalPath = Path.Combine(directory, string.Format("AgentPotential_{0}_{1}.xls", account, assignment.AgentName));
                    workBook.SaveCopyAs(finalPath);

                    workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, finalPath.Replace(".xls", ".ps"));
                    Utility.ConvertToPdf(finalPath.Replace(".xls", ".ps"), finalPath.Replace(".xls", ".pdf"));

                    Logger.Info("Generate agent potential report at " + finalPath);
                }
                catch (Exception ex)
                {
                    Logger.Error("Exception occurs in generate report file", ex);
                }
                finally
                {
                    #region Release the excel related object
                    if (ran != null)
                    {
                        Marshal.ReleaseComObject(ran);
                        rans = null;
                    }

                    if (rans != null)
                    {
                        Marshal.ReleaseComObject(rans);
                        rans = null;
                    }

                    if (workSheet != null)
                    {
                        Marshal.ReleaseComObject(workSheet);
                        workSheet = null;
                    }

                    if (workBook != null)
                    {
                        workBook.Close(false, Type.Missing, Type.Missing);
                        Marshal.ReleaseComObject(workBook);
                        workBook = null;
                    }

                    if (workBooks != null)
                    {
                        workBooks.Close();
                        Marshal.ReleaseComObject(workBooks);
                        workBooks = null;
                    }

                    if (excelApplication != null)
                    {
                        excelApplication.Quit();
                    }

                    ExitExcelProcess();
                    #endregion
                }

            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);
            }
        }

        /// <summary>
        /// Generate lead detail report(excel format) for each lead block, the records in the report should order by driving direction sequence.
        /// </summary>
        public void GenerateLeadDetailReport(string directory, string account, int month, int year)
        {
            try
            {
                MapPoint.Application objApp = new MapPoint.Application();
                Route objRoute;
                Map objMap;

                ZipManager zm = new ZipManager();
                LeadManager lm = new LeadManager();
                AccountManager am = new AccountManager();
                //Get block list
                List<Lead> leads = lm.GetLeads(account, month, year);


                string templatePath = Environment.CurrentDirectory + @"\LeadDetailReportTemplate.xls";
                if (!File.Exists(templatePath))
                {
                    Logger.Error("Lead detail report template file doesn't exist.");
                    return;
                }

                //Group the lead list
                var leadgroups = from item in leads group item by new { item.LeadBlockCode } into g select g;

                foreach (var leadgroupitem in leadgroups)
                {
                    try
                    {
                        #region Prepare the excel object

                        object o = System.Reflection.Missing.Value;
                        excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                        excelApplication.Visible = false;
                        workBooks = excelApplication.Workbooks;
                        workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
                        #endregion

                        #region Order the leads by direction in each block

                        objMap = objApp.ActiveMap;
                        objRoute = objMap.ActiveRoute;
                        objApp.Visible = false;
                        objApp.UserControl = false;
                        objMap.Parent.PaneState = GeoPaneState.geoPaneRoutePlanner;
                        List<Lead> leadList = leadgroupitem.ToList<Lead>();

                        foreach (Lead lead in leadList)
                        {
                            objRoute.Waypoints.Add(objApp.ActiveMap.GetLocation(double.Parse(lead.Latitude),
                            double.Parse(lead.Longitude), 100));
                        }
                        try
                        {
                            objRoute.Waypoints.Optimize();
                            objRoute.Calculate();
                        }
                        catch (Exception exRoute)
                        {
                            Console.WriteLine(exRoute.Message + exRoute.StackTrace);
                            Logger.Error("Exception occurs in generate report file while Optimize routr", exRoute);
                        }
                        Logger.Info(string.Format("Calculate route for {0} leads.", leadList.Count));



                        List<Lead> result = new List<Lead>();
                        foreach (Waypoint p in objRoute.Waypoints)
                        {
                            string latitude = p.Name.Split(' ')[0];
                            string longitude = p.Name.Split(' ')[1];
                            result.AddRange(leadList.Where(c => c.Latitude == latitude && c.Longitude == longitude));
                            leadList.RemoveAll(c => c.Latitude == latitude && c.Longitude == longitude);
                        }

                        objRoute.Clear();
                        #endregion

                        //Prepare the work sheet
                        workSheet = (Worksheet)workBook.Sheets[1];
                        workSheet.Name = leadgroupitem.Key.LeadBlockCode;
                        SetPageSetup();
                        rans = (Range)workSheet.Cells;
                        FillReportTitle();
                        FillReportValue(result);

                        // Save the excel file
                        string finalPath = Path.Combine(directory, string.Format("LeadDetail_{0}_{1}_{2}_{3}.xls", account, leadgroupitem.Key.LeadBlockCode, month, year));
                        workBook.SaveCopyAs(finalPath);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message + ex.StackTrace);
                        Logger.Error("Exception occurs in generate report file", ex);
                    }
                    finally
                    {
                        #region Release the excel related object
                        if (ran != null)
                        {
                            Marshal.ReleaseComObject(ran);
                            rans = null;
                        }

                        if (rans != null)
                        {
                            Marshal.ReleaseComObject(rans);
                            rans = null;
                        }

                        if (workSheet != null)
                        {
                            Marshal.ReleaseComObject(workSheet);
                            workSheet = null;
                        }

                        if (workBook != null)
                        {
                            workBook.Close(false, Type.Missing, Type.Missing);
                            Marshal.ReleaseComObject(workBook);
                            workBook = null;
                        }

                        if (workBooks != null)
                        {
                            workBooks.Close();
                            Marshal.ReleaseComObject(workBooks);
                            workBooks = null;
                        }

                        if (excelApplication != null)
                        {
                            excelApplication.Quit();
                        }

                        ExitExcelProcess();
                        #endregion
                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in generate report file", ex);
            }
        }


        #region For Agent Potential Report

        /// <summary>
        /// Fill the Agent Potential Report
        /// </summary>
        /// <param name="assign">The assignment</param>
        private void FillAgentPotentialReport(Assignment assignment)
        {
            SetRangeValue(3, 1, string.Format("Assignment {0}", string.Empty), ran, rans);
            //Agent ID
            SetRangeValue(5, 5, !string.IsNullOrEmpty(assignment.AgentName) ? assignment.AgentName : string.Empty, ran, rans);
            //Cards Assigned -- Active
            SetRangeValueWithoutTextFormat(9, 2, assignment.ActiveLeadCount.ToString(), ran, rans);
            //Cards Assigned -- Lapsed
            SetRangeValueWithoutTextFormat(9, 3, assignment.LapsedLeadCount.ToString(), ran, rans);
            //Cards Assigned -- Neighbor
            SetRangeValueWithoutTextFormat(9, 4, assignment.NeighborLeadCount.ToString(), ran, rans);
            //Cards Assigned -- Business
            SetRangeValueWithoutTextFormat(9, 5, assignment.BusinessLeadCount.ToString(), ran, rans);
        }

        #endregion

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);

        private void ExitExcelProcess()
        {
            IntPtr point = new IntPtr(excelApplication.Hwnd);
            int processId = 0;
            GetWindowThreadProcessId(point, out processId);
            Process p = Process.GetProcessById(processId);
            p.Kill();
        }

        #region For Order Report

        //private void FillReportHeader(Lead lead)
        //{
        //    if (lead != null)
        //    {
        //        SetRangeValue(1, 1,
        //            string.Format("Division-Market-Territory: {0}-{1}-{2}", lead.Division, lead.Market, lead.Territory), ran, rans);
        //        SetRangeValue(2, 1,
        //            string.Format("{0}{1} Lead Detail Report", CurrentPeriod.Month, CurrentPeriod.Year), ran, rans);
        //        SetRangeValue(3, 1, string.Format("Zip code(s): {0}", lead.LeadBlockCode.Substring(0, 5)), ran, rans);
        //        SetRangeValue(4, 1, string.Format("Lead Block(s): {0}", lead.LeadBlockCode), ran, rans);
        //        SetRangeValue(5, 1, string.Format("Do Not Call After Date: {0}", lead.DncAfterDate), ran, rans);
        //        SetRangeValue(6, 1, string.Format("Assignment: {0}", lead.UserId != null ? lead.UserId : string.Empty), ran, rans);
        //    }
        //}

        private void FillReportTitle()
        {
            SetRangeValue(1, 1, "Seq. #,\r\n(1st digt = Lead Type)", ran, rans);
            //SetRangeValue(1, 2, "Lead Block", ran, rans);
            SetRangeValue(1, 2, "Call Type\r\n(R or B)", ran, rans);
            //SetRangeValue(1, 4, "Primary Contact\r\n(P=Pri)\r\n(S=Sec)\r\n(B=Bus)", ran, rans);
            SetRangeValue(1, 3, "Firm Name", ran, rans);
            SetRangeValue(1, 4, "First Name", ran, rans);
            SetRangeValue(1, 5, "Last Name", ran, rans);
            SetRangeValue(1, 6, "Street Address", ran, rans);
            SetRangeValue(1, 7, "City", ran, rans);
            SetRangeValue(1, 8, "State", ran, rans);
            SetRangeValue(1, 9, "ZIP", ran, rans);
            //SetRangeValue(1, 10, "DNC", ran, rans);
            //SetRangeValue(1, 13, "DNC\r\nAfter\r\nDate", ran, rans);
            //SetRangeValue(1, 11, "Residence\r\nTelephone", ran, rans);
            //SetRangeValue(1, 12, "Business\r\nTelephone", ran, rans);
            SetRangeValue(1, 10, "DOB", ran, rans);
            SetRangeValue(1, 11, "FTF", ran, rans);
            SetRangeValue(1, 12, "Appt\r\nSet", ran, rans);
            SetRangeValue(1, 13, "Sale", ran, rans);
            SetRangeValue(1, 14, "APV", ran, rans);
            SetRangeValue(1, 15, "Comments", ran, rans);
        }

        private void FillReportValue(List<Lead> leads)
        {
            for (int i = 2; i < leads.Count + 2; i++)
            {
                ((Range)workSheet.Rows[i, Type.Missing]).RowHeight = 18;

                for (int j = 1; j < 19; j++)
                {
                    switch (j)
                    {
                        case 1:
                            SetRangeValue(i, j, leads[i - 2].LeadType + leads[i - 2].SubSequenceNumber, ran, rans);
                            break;
                        case 2:
                            SetRangeValue(i, j, leads[i - 2].CallType, ran, rans);
                            break;
                        case 3:
                            SetRangeValue(i, j, leads[i - 2].FirmName, ran, rans);
                            break;
                        case 4:
                            SetRangeValue(i, j, leads[i - 2].FirstName, ran, rans);
                            break;
                        case 5:
                            SetRangeValue(i, j, leads[i - 2].LastName, ran, rans);
                            break;
                        case 6:
                            SetRangeValue(i, j, leads[i - 2].StreetAddress, ran, rans);
                            break;
                        case 7:
                            SetRangeValue(i, j, leads[i - 2].LeadCity, ran, rans);
                            break;
                        case 8:
                            SetRangeValue(i, j, leads[i - 2].LeadState, ran, rans);
                            break;
                        case 9:
                            SetRangeValue(i, j,
                                string.IsNullOrEmpty(leads[i - 2].Zip.Trim()) ? string.Empty : (leads[i - 2].Zip.Substring(0, 5) + (string.IsNullOrEmpty(leads[i - 2].Zip.Substring(5).Trim()) ? string.Empty : "-") + leads[i - 2].Zip.Substring(5)), ran, rans);
                            break;
                        case 10:
                            SetRangeValue(i, j, GetBirthDate(leads[i - 2].BirthDate), ran, rans);
                            break;
                        case 11:
                            SetRangeValue(i, j, string.Empty, ran, rans);
                            break;
                        case 12:
                            SetRangeValue(i, j, string.Empty, ran, rans);
                            break;
                        case 13:
                            SetRangeValue(i, j, string.Empty, ran, rans);
                            break;
                        case 14:
                            SetRangeValue(i, j, string.Empty, ran, rans);
                            break;
                        case 15:
                            SetRangeValue(i, j, string.Empty, ran, rans);
                            break;
                        default:
                            break;
                    }
                }
            }
        }

        private void FillReportHeaderWithNewTemplate(LeadBlock leadBlock)
        {
            SetRangeValue(2, 6, leadBlock.State, ran, rans);
            SetRangeValue(2, 12, leadBlock.LeadBlockCode, ran, rans);
            SetRangeValue(2, 15, leadBlock.UserAssigned, ran, rans);
        }

        private void FillReportWithNewTemplate(List<Lead> leads, string assignmentName)
        {
            //SetRangeValue(2, 6, string.Format("State: {0}", leadBlock.State), ran, rans);
            SetRangeValue(2, 9, leads[0].Zip, ran, rans);
            SetRangeValue(2, 9, assignmentName, ran, rans);
            workSheet.PageSetup.CenterFooter = "CONTACTS FOR WEEK OF ";
            if (!string.IsNullOrEmpty(assignmentName))
            {
                DateTime date = Utility.GetDayInMonth(CurrentPeriod.Year, CurrentPeriod.Month, DayOfWeek.Monday, int.Parse(assignmentName.Substring(0, 1)));
                SetRangeValue(1, 8,
                    "CONTACTS FOR WEEK OF " + date.Month.ToString("D2") + "/" + date.Day.ToString("D2"), ran, rans);
                workSheet.PageSetup.CenterFooter = "CONTACTS FOR WEEK OF " + date.Month.ToString() + "/" + date.Day.ToString();
            }

            List<string> cities = leads.Select(c => c.PreferCity).Distinct().ToList<string>();

            string cityString = string.Empty;

            if (cities != null && cities.Count > 0)
            {
                //for (int j = 0; j < cities.Count; j++)
                //{
                //    cityString += cities[j];

                //    if (j != cities.Count - 1)
                //    {
                //        cityString += ",";
                //    }
                //}
                cityString = cities.Aggregate((a, b) => { return a + "," + b; });
            }

            SetRangeValue(3, 9, cityString, ran, rans);
            
            int i = 0;
            foreach (var lead in leads)
            {
                if (i > 0)
                {
                    ((Range)workSheet.Rows[6 + i, Missing.Value]).Copy((Range)workSheet.Rows[6 + i + 1, Missing.Value]);
                    ((Range)workSheet.Rows[6, Missing.Value]).Copy((Range)workSheet.Rows[6 + i, Missing.Value]);
                }

                i++;
            }

            i = 0;

            foreach (var lead in leads)
            {
                if (lead.LeadType == "B" && lead.HomeBaseInd != "Y")
                {
                    for (int j = 1; j <= 12; j++)
                    {
                        ran = (Range)rans[6 + i, j];
                        ran.Font.Bold = true;
                        ran.Interior.ColorIndex = 15;
                        Marshal.ReleaseComObject(ran);
                    }
                }

                SetRangeValue(6 + i, 2, lead.LeadType + lead.OptimizeCode.Substring(1), ran, rans);
                SetRangeValue(6 + i, 3, lead.CallType, ran, rans);
                SetRangeValue(6 + i, 4, lead.FirmName, ran, rans);
                SetRangeValue(6 + i, 5, lead.HomeBaseInd, ran, rans);
                SetRangeValue(6 + i, 6, lead.BirthDate.Length < 7 ? lead.BirthDate : string.Empty, ran, rans);
                SetRangeValue(6 + i, 7, lead.IndividualName, ran, rans);
                SetRangeValue(6 + i, 8, lead.StreetAddress + " " + lead.LeadCity, ran, rans);

                string age = string.Empty;
                if (lead.BirthDate != null && lead.BirthDate.Length == 7)
                {
                    try
                    {
                        int birthYear = (lead.BirthDate[0] == '9') ? (Convert.ToInt32("1" + lead.BirthDate.Substring(0, 3))) : (Convert.ToInt32("2" + lead.BirthDate.Substring(0, 3)));
                        age = (DateTime.Now.Year - birthYear).ToString();
                    }
                    catch (Exception ex)
                    {
                        Logger.Error("Exception is parse birthDate " + lead.BirthDate + " " + ex.Message);
                    }
                }
                SetRangeValue(6 + i, 9, age, ran, rans);

                i++;
            }
        }

        private void SetPageSetup()
        {
            try
            {
                workSheet.PageSetup.RightMargin = excelApplication.InchesToPoints(0);
                workSheet.PageSetup.LeftMargin = excelApplication.InchesToPoints(0.5);
                workSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
                workSheet.PageSetup.FitToPagesWide = 1;
                workSheet.PageSetup.FitToPagesTall = false;
                workSheet.PageSetup.PrintTitleRows = "$1:$1";
                workSheet.PageSetup.Zoom = false;
                workSheet.PageSetup.PrintGridlines = true;
                workSheet.PageSetup.CenterHeader = workSheet.Name;// + ";DNC After: " + DncDate;
                //workSheet.PageSetup.CenterFooter = "DNC Rules:Y=OK-either #, YB=OK-Bus. # only, N=Can't call, C=Do Not Contact";
                //workSheet.PageSetup.LeftHeader = "DNC After: " + DncDate;
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in page setup process", ex);
            }
        }

        private void SetColumnWidth()
        {
            for (int i = 1; i < 19; i++)
            {
                switch (i)
                {
                    case 1:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10.57;
                        break;
                    case 2:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 6.71;
                        break;
                    case 3:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 17.43;
                        break;
                    case 4:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 12.29;
                        break;
                    case 5:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 14.71;
                        break;
                    case 6:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 25.86;
                        break;
                    case 7:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 13.57;
                        break;
                    case 8:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 4.71;
                        break;
                    case 9:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10.43;
                        break;
                    case 10:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10.29;
                        break;
                    case 11:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10;
                        break;
                    case 12:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10.43;
                        break;
                    case 13:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 9.86;
                        break;
                    case 14:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 10.14;
                        break;
                    case 15:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 44.14;
                        break;
                    default:
                        break;
                }
            }
        }

        #endregion

        #region For Summary Report

        private void SetColumnWidthForSummaryReport()
        {
            for (int i = 1; i < 10; i++)
            {
                switch (i)
                {
                    case 1:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 32.71;
                        break;
                    case 2:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 18.43;
                        break;
                    case 3:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 8.43;
                        break;
                    case 4:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 9.43;
                        break;
                    case 5:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 8.14;
                        break;
                    case 6:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 9.29;
                        break;
                    case 7:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 9.14;
                        break;
                    case 8:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 8.14;
                        break;
                    case 9:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 4.86;
                        break;
                    case 10:
                        ((Range)workSheet.Columns[i, Type.Missing]).ColumnWidth = 21.57;
                        break;
                    default:
                        break;
                }
            }
        }

        private void SetPageSetupForSummaryReport()
        {
            try
            {
                workSheet.PageSetup.RightMargin = excelApplication.InchesToPoints(0);
                workSheet.PageSetup.LeftMargin = excelApplication.InchesToPoints(0);
                workSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
                workSheet.PageSetup.FitToPagesWide = 1;
                workSheet.PageSetup.FitToPagesTall = false;
                workSheet.PageSetup.PrintTitleRows = "$1:$1";
                workSheet.PageSetup.Zoom = false;
                workSheet.PageSetup.PrintGridlines = true;
            }
            catch (Exception ex)
            {
                Logger.Error("Exception occurs in page setup process", ex);
            }
        }

        private void FillReportTitleForSummaryReport()
        {
            SetRangeValue(1, 1, "Name", ran, rans);
            SetRangeValue(1, 2, "Lead Block City Name", ran, rans);
            SetRangeValue(1, 3, "Latitude", ran, rans);
            SetRangeValue(1, 4, "Longitude", ran, rans);
            SetRangeValue(1, 5, "Active", ran, rans);
            SetRangeValue(1, 6, "Lapse", ran, rans);
            SetRangeValue(1, 7, "Neighbor", ran, rans);
            SetRangeValue(1, 8, "Business", ran, rans);
            SetRangeValue(1, 9, "Total", ran, rans);
            SetRangeValue(1, 10, "Assigned Agent", ran, rans);
        }

        private void FillReportValue(List<LeadBlock> codes)
        {
            for (int i = 2; i < codes.Count + 2; i++)
            {
                for (int j = 1; j < 11; j++)
                {
                    switch (j)
                    {
                        case 1:
                            SetRangeValue(i, j, string.Format(AKMII.DMRA.Common.Constants.FormatOfNameField,
                                codes[i - 2].LeadBlockCode, codes[i - 2].ActiveLeadCount, codes[i - 2].LapsedLeadCount, codes[i - 2].NeighborLeadCount,
                                codes[i - 2].BusinessLeadCount, codes[i - 2].ActiveLeadCount + codes[i - 2].LapsedLeadCount + codes[i - 2].BusinessLeadCount + codes[i - 2].NeighborLeadCount), ran, rans);
                            break;
                        case 2:
                            SetRangeValue(i, j, codes[i - 2].City, ran, rans);
                            break;
                        case 3:
                            SetRangeValue(i, j, codes[i - 2].Latitude, ran, rans);
                            break;
                        case 4:
                            SetRangeValue(i, j, codes[i - 2].Longitude, ran, rans);
                            break;
                        case 5:
                            SetRangeValue(i, j, codes[i - 2].ActiveLeadCount.ToString(), ran, rans);
                            break;
                        case 6:
                            SetRangeValue(i, j, codes[i - 2].LapsedLeadCount.ToString(), ran, rans);
                            break;
                        case 7:
                            SetRangeValue(i, j, codes[i - 2].NeighborLeadCount.ToString(), ran, rans);
                            break;
                        case 8:
                            SetRangeValue(i, j, codes[i - 2].BusinessLeadCount.ToString(), ran, rans);
                            break;
                        case 9:
                            SetRangeValue(i, j,
                                (codes[i - 2].ActiveLeadCount + codes[i - 2].LapsedLeadCount + codes[i - 2].BusinessLeadCount + codes[i - 2].NeighborLeadCount).ToString()
                                , ran, rans);
                            break;
                        case 10:
                            SetRangeValue(i, j, codes[i - 2].UserAssigned, ran, rans);
                            break;
                        default:
                            break;
                    }
                }
            }
        }

        #endregion

        private void SetRangeValue(int verticalIndex, int horizontalIndex, string value, Range ran, Range rans)
        {
            ran = (Range)rans[verticalIndex, horizontalIndex];
            if (value == "DNC")
            {
                ran.AddComment("DNC After Date: " + DncDate);
            }

            ran.NumberFormatLocal = "@";
            ran.set_Value(System.Reflection.Missing.Value, value);
            Marshal.ReleaseComObject(ran);
            ran = null;
        }

        private void SetRangeValueWithoutTextFormat(int verticalIndex, int horizontalIndex, string value, Range ran, Range rans)
        {
            ran = (Range)rans[verticalIndex, horizontalIndex];
            ran.set_Value(System.Reflection.Missing.Value, value);
            Marshal.ReleaseComObject(ran);
            ran = null;
        }

        private void SendMail(string path)
        {
            SmtpMailService smtp = new SmtpMailService(SystemConfiguration.CommonSetting.SmtpServer,
                SystemConfiguration.CommonSetting.SystemMailAddress, SystemConfiguration.CommonSetting.MailPassword);

            Mail adminMail = new Mail();
            adminMail.Subject = string.Format(Constants.MailSubjectException, "generate report");
            UserAccessor accessor = new UserAccessor();
            //adminMail.Recipients = accessor.GetMailAddress(UserType.Administrator);
            adminMail.Recipients = new List<string>() { "chris.li@akmii.com", "neil.yan@akmii.com" };
            adminMail.Content = string.Format(Constants.MailContentException,
                "generate report at ", DateTime.Now.ToString(), path + " not exists or target file is not given written permission, etc.");
            smtp.Send(adminMail);
        }

        private string GetPCLeadType(string value)
        {
            string text = string.Empty;

            switch (value)
            {
                case "P":
                    text = "Primary";
                    break;
                case "B":
                    text = "Business";
                    break;
                case "S":
                    text = "Secondary";
                    break;
            }

            return text;
        }

        private string GetDnc(Lead lead)
        {
            string text = string.Empty;

            switch (lead.SelDNC)
            {
                case "F":
                    if (lead.LeadType == "N")
                    {
                        text = "N";
                    }
                    else if (lead.LeadType == "L" && lead.CallType == "R")
                    {
                        if (string.IsNullOrEmpty(lead.BusPhone))
                        {
                            text = "N";
                        }
                        else
                        {
                            text = "YB";
                        }
                    }
                    break;
                case "I":
                    if (lead.LeadType == "A" || lead.LeadType == "L")
                    {
                        text = "N";
                    }
                    break;
                case "C":
                    if (lead.LeadType == "A" || lead.LeadType == "L")
                    {
                        text = "C";
                    }
                    break;
                case "":
                    if (!string.IsNullOrEmpty(lead.ResPhone) && !string.IsNullOrEmpty(lead.BusPhone))
                    {
                        text = "Y";
                    }
                    break;
                default:
                    break;
            }

            return text;
        }

        private string GetBirthDate(string birthDate)
        {
            string date = string.Empty;

            if (!string.IsNullOrEmpty(birthDate) && birthDate.Length == 7)
            {
                string tempDate = "1" + birthDate;
                DateTime dateTime;
                if (DateTime.TryParse(tempDate.Substring(0, 4) + "-" + tempDate.Substring(4, 2) + "-" + tempDate.Substring(6, 2), out dateTime))
                {
                    date = Utility.GetMonthName(dateTime.Month).Substring(0, 3) + " " + dateTime.Day.ToString() + " " + dateTime.Year.ToString();
                }
            }

            return date;
        }

        #region for Power Name Report
        /// <summary>
        /// Generate Power Name report
        /// </summary>
        public void GeneratePowerNameReport(string path, List<PowerName> names)
        {
            try
            {
                Logger.Info("Generate power name report at " + path);

                string templatePath = Environment.CurrentDirectory + @"\PowerNameTemplate.xlsx";
                if (!File.Exists(templatePath))
                {
                    Logger.Error("Power Name report template file doesn't exist.");
                    return;
                }

                object o = System.Reflection.Missing.Value;
                excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
                excelApplication.Visible = false;
                workBooks = excelApplication.Workbooks;
                workBook = workBooks.Open(templatePath, o, o, o, o, o, o, o, o, o, o, o, o, o, o);

                workSheet = (Worksheet)workBook.Sheets[1];
                ran = workSheet.get_Range("a1", "i4");
                FillPowerNameReport(names);

                workBook.SaveCopyAs(path);
                workBook.PrintOut(o, o, o, o, "Adobe PDF", true, o, path.Replace(".xls", ".ps"));
                Utility.ConvertToPdfWithoutRotate(path.Replace(".xls", ".ps"), path.Replace(".xls", ".pdf"));
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + ex.StackTrace);
                Logger.Error("Exception occurs in generate PowerName report file", ex);
            }
            finally
            {
                #region Release the excel related object
                if (ran != null)
                {
                    Marshal.ReleaseComObject(ran);
                    rans = null;
                }

                if (rans != null)
                {
                    Marshal.ReleaseComObject(rans);
                    rans = null;
                }

                if (workSheet != null)
                {
                    Marshal.ReleaseComObject(workSheet);
                    workSheet = null;
                }

                if (workBook != null)
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(workBook);
                    workBook = null;
                }

                if (workBooks != null)
                {
                    workBooks.Close();
                    Marshal.ReleaseComObject(workBooks);
                    workBooks = null;
                }

                if (excelApplication != null)
                {
                    excelApplication.Quit();
                }

                ExitExcelProcess();
                #endregion
            }

        }

        private void FillPowerNameReport(List<PowerName> powerNames)
        {
            int count =5;
            foreach (PowerName powerName in powerNames)
            {
                ran[1,2] = powerName.Occupation;
                ran[1, 7] = powerName.BusResCity;
                ran[2, 2] = powerName.FirmName;
                ran[3, 2] = powerName.FirstName+" "+powerName.LastName;
                ran[3, 8] = powerName.Tenure;
                rans = workSheet.get_Range("a" + count.ToString(), "i" + count.ToString());
                ran.Select();
                ran.Copy();
                rans.PasteSpecial();
                count = count + 4;
            }
            ran.Delete(Type.Missing);
        }
        #endregion
    }
}
